<?php
// +----------------------------------------------------------------------
// | quickadmin框架 [ quickadmin框架 ]
// +----------------------------------------------------------------------
// | 版权所有 2020~2022 南京新思汇网络科技有限公司
// +----------------------------------------------------------------------
// | 官方网站: https://www.quickadmin.top
// +----------------------------------------------------------------------
// | Author: zs <909883663@qq.com>
// +----------------------------------------------------------------------
namespace app\admin\controller;

use app\admin\model\Site;
use app\BaseController;
use app\dic\DicOrder;
use app\dic\DicProduct;
use app\dic\DicPublic;
use app\dic\DicSite;
use app\dic\OrderDic;
use think\App;
use think\facade\Db;
use util\Excel;
use think\cache\driver\Redis;

use util\Token;
/*
 * @Description: index
 * @Date: 2021-05-25 14:57:43
 */

class Dashboard extends BaseController
{

    protected $model = null;

    public function __construct(App $app)
    {
        parent::__construct($app);

        $this->model = new Site();
    }

    //首页统计运营数据
    
    public function dashboard_new()
    {
        $post = $this->request->post();
        if(empty($post)){
            $post['day_changes'] = 0;
            $post['start_time'] = strtotime(date('Y-m-d h:i:s', time() - 15 * 3600));
            $post['end_time'] = strtotime(date('Y-m-d h:i:s', time() + 9 * 3600));
        }
        $start_time = !empty($post['start_time']) ? $post['start_time'] : strtotime(date('Y-m-d 00:00:00', time() - 15 * 3600));
        $start_time = strtotime(date('Y-m-d 00:00:00', $start_time));
        if ($post['day_changes'] == 3) {
            $start_time = strtotime(date('Y-m-1 00:00:00', $start_time));
        }
        $start_time_str = date('Y-m-d H:i:s', $start_time);
        $end_time = !empty($post['end_time']) ? $post['end_time'] : strtotime(date('Y-m-d 00:00:00', time() + 9 * 3600));
        if ($post['day_changes'] == 101) {
            $end_time = strtotime(date('Y-m-d 23:59:59', $end_time));
        } else {
            $end_time = strtotime(date('Y-m-d 00:00:00', $end_time));
        }
        $end_time_str =  date('Y-m-d H:i:s', $end_time);
        $redis = new Redis();
        $dashboards = $redis->get('dashboard-new-' . $start_time . '-' . $end_time . '-' . $post['day_changes']);
        if (!empty($dashboards)) {
            $data = [
                'code' => 1,
                'msg' => '缓存数据',
                'data' => $dashboards,
            ];
            return json($data);
        }

        $exchange = $redis->get('exchangerate');
        if(empty($exchange)){
            $exchange = table('exchange')
                ->where('currency','USD')
                ->order('date desc')
                ->find();
            $redis->set('exchangerate', $exchange, 60 * 60 * 24 );
        }
        $exchange_eur = $redis->get('exchangerate_eur');
        if(empty($exchange_eur)){
            $exchange_eur = table('exchange')
                ->where('currency','EUR')
                ->order('date desc')
                ->find();
            $redis->set('exchangerate_eur', $exchange_eur, 60 * 60 * 24 );
        }
        $exchange_mxn = $redis->get('exchangerate_mxn');
        if(empty($exchange_mxn)){
            $exchange_mxn = table('exchange')
                ->where('currency','MXN')
                ->order('date desc')
                ->find();
            $redis->set('exchangerate_mxn', $exchange_mxn, 60 * 60 * 24 );
        }

        $where[]  = ['created_at', '>=', $start_time];
        $where[]  = ['created_at', '<=', $end_time];
        $where_total_price[] = ['financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
        //已支付 + 部分退款 金额
        $where2[]  = ['a.created_at', '>=', $start_time];
        $where2[]  = ['a.created_at', '<=', $end_time];
        $where_total_price2[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
        $statistics = table('order')
            ->field('sum(a.total_price) as total_price,sum(a.freight) as freight,sum(a.material) as material')
            ->alias('a')
            ->join('qu_site b', 'b.id = a.site_id', 'left')
            ->where($where2)
            ->where($where_total_price2)
            ->where(['a.status' => DicSite::$status_normal])
            ->find(); //总销售额

        $statistics1 = table('order')
            ->field('sum(a.total_price) as total_price,currency')
            ->alias('a')
            ->join('qu_site b', 'b.id = a.site_id', 'left')
            ->where($where2)
            ->where($where_total_price2)
            ->where(['a.status' => DicSite::$status_normal])
            ->group('currency')
            ->select()->toarray(); //总销售额
        $arr['sales'] = 0;
        $arr['sales_eur'] = 0;
        $arr['sales_usd'] = 0;
        $arr['sales_mxn'] = 0;
        if(!empty($statistics1)){
            foreach ($statistics1 as $ks=>$vs) {
                if($vs['currency'] == 'EUR'){
                    $arr['sales_eur'] = (float)number_format((float)$vs['total_price'], 3, '.', '');
                    $sales_eur = (float)number_format((float)$arr['sales_eur'] * (float)$exchange_eur['proportion'] / (float)$exchange['proportion'], 3, '.', '');
                    $arr['sales'] = $arr['sales'] + $sales_eur;
                }
                if($vs['currency'] == 'USD'){
                    $arr['sales_usd'] = (float)number_format((float)$vs['total_price'], 3, '.', '');
                    $arr['sales'] = $arr['sales'] + $arr['sales_usd'];
                }
                if($vs['currency'] == 'MXN'){
                    $arr['sales_mxn'] = (float)number_format((float)$vs['total_price'], 3, '.', '');
                    $sales_mxn = (float)number_format((float)$arr['sales_mxn'] * (float)$exchange_mxn['proportion'] / (float)$exchange['proportion'], 3, '.', '');
                    $arr['sales'] = $arr['sales'] + $sales_mxn;
                }
            }
        }

        $arr['sales'] = (float)number_format((float)$arr['sales'], 2, '.', '');
        $arr['platform_discount'] = (float)number_format((float)$arr['sales']*0.06, 3, '.', '');
        $arr['logistics'] = (float)number_format((float)$statistics['freight'] / (float)$exchange['proportion'], 3, '.', '');
        $arr['purchase'] = (float)number_format((float)$statistics['material'] / (float)$exchange['proportion'], 3, '.', '');

        $arr['paid'] = table('order')
            ->alias('a')
            ->join('qu_site b', 'b.id = a.site_id', 'left')
            ->where($where2)
            ->where($where_total_price2)
            ->where(['a.status' => DicSite::$status_normal])
            ->count(); //支付订单
        $arr['authorized'] = table('order')
            ->alias('a')
            ->join('qu_site b', 'b.id = a.site_id', 'left')
            ->where($where2)
            ->where(['a.financial_status' => 'authorized'])
            ->count();
        //退款
        $where_refund[]  = ['processed_at', '>=', $start_time];
        $where_refund[]  = ['processed_at', '<=', $end_time];
        $where_refund[]  = ['refund_status', '<=', 'success'];
        $arr['refund'] = table('order_refunds')->where($where_refund)->count(); //退款订单
        $arr['refund_money'] = number_format(table('order_refunds')->where($where_refund)->sum('amount'), 2, '.', ''); //退款金额

        //争议单
        $where_dispute[]  = ['createtime', '>=', $start_time];
        $where_dispute[]  = ['createtime', '<=', $end_time];
        $arr['dispute'] = table('order_dispute')->where($where_dispute)->count();
        //FB广告消耗金额图表

        $adv_where[]  = ['create_time', '>=', $start_time_str];
        $adv_where[]  = ['create_time', '<', $end_time_str];
        $arr['adv_cost'] = 0;

        $shopify_sales_arr = array();
        //店铺销售额
        $shopify_sales = table('order')->field('site_id,sum(total_price) as total,sum(countnum) as count,currency,sum(freight) as freight,sum(material) as material')->where($where)->where($where_total_price)->group('site_id')->order('total desc')->select()->toarray(); //总销售额

        $site_total = table('site')
            ->field('id,site_name')
            ->where('status','normal')
            ->select()->toarray();
        $site_total_new = array();
        foreach ($site_total as $k=>$v) {
            $site_total_new[$v['id']] = $v['site_name'];
        }
        if (!empty($shopify_sales)) {
            foreach ($shopify_sales as $key => $value) {
                $advplatform = table('site')->where(['id' => $value['site_id']])->find();

                $wheregroup = array();
                $wheregroup[]  = ['a.updatetime', '>=', $end_time];
                $wheregroup['a.site_id'] = $value['site_id'];
                $wheregroup['a.type'] = '主运营';
                $site_log = table('site_log')
                    ->field("b.nickname")
                    ->alias('a')
                    ->join('qu_system_admin b', 'b.id = a.admin_id', 'left')
                    ->where($wheregroup)
                    ->find();
                if(!isset($site_log['nickname']) || empty($site_log['nickname'])){
                    $site_log['nickname'] = '';
                }
                if (!empty($advplatform)) {
                    $site_name = $advplatform['site_name'];
                } else {
                    $site_name = '未知';
                }
                unset($site_total_new[$value['site_id']]);
                $zsales = (float)number_format($value['total'], 3, '.', '');
                $shopify_sales_remarks = '';
                if($value['currency'] == 'EUR'){
                    $shopify_sales_remarks = '原销售额为€'.$zsales;
                    $zsales = (float)number_format((float)$zsales * (float)$exchange_eur['proportion'] / (float)$exchange['proportion'], 3, '.', '');
                }
                if($value['currency'] == 'MXN'){
                    $shopify_sales_remarks = '原销售额为Mex$'.$zsales;
                    $zsales = (float)number_format((float)$zsales * (float)$exchange_mxn['proportion'] / (float)$exchange['proportion'], 3, '.', '');
                }
                $zcount = (int)$value['count'];
                //时间段退款
                $sales_refund = table('order_refunds')
                    ->where($where_refund)
                    ->where(['site_id' => $value['site_id']])
                    ->sum('amount');
                $sales_refund = (float)number_format($sales_refund, 3, '.', '');
                //显示平台销售额

                // remarks
                // $sales_remarks = table('order')
                //     ->field("SUM(`countnum`) as countnum,tags")
                //     ->where(['site_id' => $value['site_id']])
                //     ->where($where)
                //     ->where($where_total_price)
                //     ->group('tags') 
                //     ->select()
                //     ->toarray();
                // if(!empty($sales_remarks)){
                //     foreach ($sales_remarks as $kr=>$vr) {
                //         if($vr['tags'] == '暂无'){
                //             $vr['tags'] = '主运营';
                //             if(!empty($shopify_sales_remarks)){
                //                 $shopify_sales_remarks = $shopify_sales_remarks.' |
                //                     '.$vr['tags'].'('.$vr['countnum'].'单)';
                //             }else{
                //                 $shopify_sales_remarks = $vr['tags'].'('.$vr['countnum'].'单)';
                //             }
                //         }else{
                //             // continue;
                //         }
                //     }
                // }
                $zadvcost_z2 = array(); 
                $zadvcost_z3 = array(); 
                $advplat_arr = table('advplat')
                    ->where('site_id',$value['site_id'])
                    ->select()->toarray();
                $zadvcost_new = 0;
                if(!empty($advplat_arr)){
                    foreach ($advplat_arr as $k0=>$v0) {
                        if($v0['adv_id'] == 1){
                            $zadvcost_z2 = table('advertising')
                                ->where($adv_where)
                                ->where('plat_account',$v0['id'])
                                ->where('status','normal')
                                ->select()->toarray();
                                if(!empty($zadvcost_z2)){
                                    foreach ($zadvcost_z2 as $k1=>$v1) {
                                        $zadvcost_new = (float)number_format((float)$zadvcost_new + (float)$v1['cost'], 3, '.', '');
                                    }
                                }
                        }else{
                            $zadvcost_z3 = table('advertising')
                                ->where($adv_where)
                                ->where('plat_account',$v0['id'])
                                ->where('status','normal')
                                ->select()->toarray();
                                if(!empty($zadvcost_z3)){
                                    foreach ($zadvcost_z3 as $k2=>$v2) {
                                        $zadvcost_new = (float)number_format((float)$zadvcost_new + (float)$v2['cost'], 3, '.', '');
                                    }
                                }
                        }
                    }
                }
                $arr['adv_cost'] = (float)number_format((float)$zadvcost_new + (float)$arr['adv_cost'], 3, '.', '');
                if ($zsales > 0.02 && $zadvcost_new > 0.02) {
                    $roi = (float)number_format((float)$zsales / (float)$zadvcost_new, 3, '.', '');
                }else{
                    $roi = 0;
                }

                
                $gross_profit = (float)number_format((float)$zsales*0.94 - (float)$value['freight'] - (float)$value['material'] - $zadvcost_new, 3, '.', '');
                $shopify_sales_arr[] = [
                    'id' => $value['site_id'],
                    'name' => $site_name,
                    'gross_profit' => $gross_profit,
                    'count' => $zcount,
                    'sales' => $zsales,
                    'refund' => $sales_refund,
                    'nickname' => $site_log['nickname'],
                    'zadvcost_new' => $zadvcost_new,
                    'roi' => $roi,
                    'remarks' => $shopify_sales_remarks,
                ];
            }
        }
        // halt($site_total_new);
        foreach ($site_total_new as $kst => $vst) {
            //时间段退款
            $sales_refund_st = table('order_refunds')
                ->where($where_refund)
                ->where(['site_id' => $kst])
                ->sum('amount');
            $sales_refund_st = (float)number_format($sales_refund_st, 3, '.', '');
            if($sales_refund_st >0.01){

                $wheregroup = array();
                $wheregroup[]  = ['a.updatetime', '>=', $end_time];
                $wheregroup['a.site_id'] = $kst;
                $wheregroup['a.type'] = '主运营';
                $site_log = table('site_log')
                    ->field("b.nickname")
                    ->alias('a')
                    ->join('qu_system_admin b', 'b.id = a.admin_id', 'left')
                    ->where($wheregroup)
                    ->find();
                if(!isset($site_log['nickname']) || empty($site_log['nickname'])){
                    $site_log['nickname'] = '';
                }
                $zadvcost_z2 = array(); 
                $zadvcost_z3 = array(); 
                $advplat_arr = table('advplat')
                    ->where('site_id',$value['site_id'])
                    ->select()->toarray();
                if(!empty($advplat_arr)){
                    foreach ($advplat_arr as $k0=>$v0) {
                        if($v0['adv_id'] == 1){
                            $zadvcost_z2 = table('advertising')
                                ->where($adv_where)
                                ->where('plat_account',$v0['id'])
                                ->where('status','normal')
                                ->select()->toarray();
                        }else{
                            $zadvcost_z3 = table('advertising')
                                ->where($adv_where)
                                ->where('plat_account',$v0['id'])
                                ->where('status','normal')
                                ->select()->toarray();
                        }
                    }
                }
                $zadvcost_new = 0;
                if(!empty($zadvcost_z2)){
                    foreach ($zadvcost_z2 as $k1=>$v1) {
                        $zadvcost_new = (float)number_format((float)$zadvcost_new + $v1['cost'], 3, '.', '');
                    }
                }
                if(!empty($zadvcost_z3)){
                    foreach ($zadvcost_z3 as $k2=>$v2) {
                        $zadvcost_new = (float)number_format((float)$zadvcost_new + $v2['cost'], 3, '.', '');
                    }
                }
                $arr['adv_cost'] = (float)number_format((float)$zadvcost_new + (float)$arr['adv_cost'], 3, '.', '');

                $gross_profit = (float)number_format((float)0 - $zadvcost_new, 3, '.', '');
                $shopify_sales_arr[] = [
                    'id' => $kst,
                    'name' => $vst,
                    'gross_profit' => $gross_profit,
                    'count' => 0,
                    'sales' => 0,
                    'refund' => $sales_refund_st,
                    'nickname' => $site_log['nickname'],
                    'zadvcost_new' => $zadvcost_new,
                    'roi' => 0,
                    'remarks' => '',
                ];
            }
        }
        //物流加成费
        $arr['gross_profit'] = (float)number_format((float)$arr['sales']*0.94 - (float)$arr['logistics'] - (float)$arr['purchase'] - (float)$arr['adv_cost'], 2, '.', '');
        $arr['roi'] = 0;
        if ($arr['adv_cost'] > 0.001 && $arr['sales'] > 0.001) {
            $arr['roi'] = number_format((float)$arr['sales'] / (float)$arr['adv_cost'], 3, '.', '');
        } //转化率ROI
        $sales_handle = '（美金$'.$arr['sales_usd'].',欧元€'.$arr['sales_eur'].'）';
        $paid_handle = '（争议单'.$arr['dispute'].',退款单'.$arr['refund'].',授权状态订单'.$arr['authorized'].'）';
        $gross_profit_handle = '（毛利'.$arr['gross_profit'].'
        =销售额'.$arr['sales'].'-物流'.$arr['logistics'].'-采购'.$arr['purchase'].'
        -广告'.$arr['adv_cost'].'-手续费(6%)'.$arr['platform_discount'].'）';
        $arr['gross_profit_handle1'] = '销售额-物流-采购-广告-手续费(6%)';
        $arr['gross_profit_handle2'] = $arr['sales'].'-'.$arr['logistics'].'-'.$arr['purchase'].'-'.$arr['adv_cost'].'-'.$arr['platform_discount'];
        $newarr = array();
        $newarr['arr'][] = $arr;
        $newarr['shopify_sales'] = $shopify_sales_arr;
        $newarr['sales_handle'] = $sales_handle;
        $newarr['paid_handle'] = $paid_handle;
        $newarr['gross_profit_handle'] = $gross_profit_handle;
        $redis->set('dashboard-new-' . $start_time . '-' . $end_time . '-' . $post['day_changes'], $newarr, 60*3);
        $data = [
            'code' => 1,
            'msg' => '',
            'data' => $newarr,
        ];
        return json($data);
    }



}
